﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Web.Mvc;
using CRM.Admin.Contracts.Contract.Customer;
using CRM.Admin.Contracts.Dto.Customer;
using CRM.Admin.Contracts.Model.Customer;
using CRM.Admin.Services.Service.Customer;
using KingsSharp.Common.Filter;
using KingsSharp.Core.Data.Entity;
using KingsSharp.Core.Data.Extensions;
using KingsSharp.Web.UI;
using NPOI.SS.UserModel;
using System.IO;

namespace CRM.Admin.Areas.Customer.Controllers
{
    public class GetCustomerDataController : Controller
    {
        /// <summary>
        /// 联系人
        /// </summary>
        public ILinkManService LinkManService { get; set; }
        public ActionResult contacts()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<LinkMan, bool>> predicate = FilterHelper.GetExpression<LinkMan>(request.FilterGroup);
            int total;
            var data = LinkManService.LinkMans.
                Where<LinkMan, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).ToList().MapToList<LinkMan, LinkManDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 顾客性质
        /// </summary>
        public ICustomerNatureService CustomerNatureService { get; set; }

        public ActionResult customerNature()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<CustomerNature, bool>> predicate = FilterHelper.GetExpression<CustomerNature>(request.FilterGroup);
            int total;
            var data = CustomerNatureService.Natures.
                Where<CustomerNature, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).Select(s => new
                {
                    s.Id,
                    s.Nature.Value,
                    NatureId = s.Nature.Id,
                    NatureCatalogName = s.Nature.NatureCatalog.Name
                }).ToList();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 生产工艺
        /// </summary>
        public ITechnologyService TechnologyService { get; set; }
        public ActionResult artsProcess()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<Technology, bool>> predicate = FilterHelper.GetExpression<Technology>(request.FilterGroup);
            int total;
            var data = TechnologyService.Technologys.
                Where<Technology, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).ToList().MapToList<Technology, TechnologyDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }


        /// <summary>
        /// 产品零件
        /// </summary>
        public IProductService ProductService { get; set; }

        public ActionResult product()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<Product, bool>> predicate = FilterHelper.GetExpression<Product>(request.FilterGroup);
            int total;
            var data = ProductService.Products.
                Where<Product, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).ToList().MapToList<Product, ProductDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }


        /// <summary>
        /// 采购渠道
        /// </summary>
        public IPurchaseService PurchaseService { get; set; }

        public ActionResult purchaseChannel()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<Purchase, bool>> predicate = FilterHelper.GetExpression<Purchase>(request.FilterGroup);
            int total;
            var data = PurchaseService.Purchases.
                Where<Purchase, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).OrderByDescending(a => a.Year).ToList().MapToList<Purchase, PurchaseDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 品种需求
        /// </summary>
        public IVarietyService VarietyService { get; set; }

        public ActionResult varietyDemand()
        {
            GridRequest request = new GridRequest(Request);

            Expression<Func<Variety, bool>> predicate = FilterHelper.GetExpression<Variety>(request.FilterGroup);
            int total;
            var data = VarietyService.Varietys
                .
                 Where(a => a.IsDeleted == false).OrderByDescending(a => a.ParticularYear).ThenByDescending(a => a.Month)
                .Where<Variety, Guid>(predicate, request.PageCondition, out total, false).
                ToList().MapToList<Variety, VarietyDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }
        /// <summary>
        /// 导出品种需求
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public ICustomerService CustomerService { get; set; }
        public FileResult ExportVariety(Guid customerId)
        {
            var list = VarietyService.Varietys.Where(a => a.CustomerId == customerId && !a.IsDeleted).ToList().MapToList<Variety, VarietyDto>();
            var customer = CustomerService.Customers.FirstOrDefault(a => a.Id == customerId);
            // 创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet($"{customer.Name}");
            ////设置默认行高
            sheet1.DefaultRowHeight = 18 * 20;
            string[] cellsArr = new string[]{
                "需求年份",
                "需求月份",
                "产品/零件名称",
                "原料材质",
                "原料规格",
                "原料单件重量(kg)",
                "当月需求量(吨)",
                "原料单件高度",
                "原料单件厚度",
                "原料单件宽度",
                "对应攀钢品种",
                "攀钢钒可覆盖量(吨)",
                "攀钢钒已供应量(吨)",
                "西钢钒可覆盖量(吨)",
                "西钢钒已供应量(吨)"
            };
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            for (int i = 0; i < cellsArr.Length; i++)
            {
                row1.CreateCell(i).SetCellValue(cellsArr[i]);
            }

            for (int i = 0; i < list.Count; i++)
            {
                var item = list[i];
                //第一个TR
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(item.ParticularYear + "");
                rowtemp.CreateCell(1).SetCellValue(item.Month + "");
                rowtemp.CreateCell(2).SetCellValue(item.DemandSourcesName);
                rowtemp.CreateCell(3).SetCellValue(item.MaterialMade);
                rowtemp.CreateCell(4).SetCellValue(item.MaterialSpecs);
                rowtemp.CreateCell(5).SetCellValue(item.MaterialWeight + "");
                rowtemp.CreateCell(6).SetCellValue(item.NowNeedWeight + "");
                rowtemp.CreateCell(7).SetCellValue(item.MaterialHeight + "");
                rowtemp.CreateCell(8).SetCellValue(item.MaterialThickness + "");
                rowtemp.CreateCell(9).SetCellValue(item.MaterialWidth + "");
                rowtemp.CreateCell(10).SetCellValue(item.BreedName);
                rowtemp.CreateCell(11).SetCellValue(item.PGFCoverWeight + "");
                rowtemp.CreateCell(12).SetCellValue(item.PGFAlreadySupplyWeight + "");
                rowtemp.CreateCell(13).SetCellValue(item.XGFCoverWeight + "");
                rowtemp.CreateCell(14).SetCellValue(item.XGFAlreadySupplyWeight + "");
            }

            //单元格对齐样式
            ICellStyle style0 = book.CreateCellStyle();
            style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//居中 
            style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            for (int a = 0; a < list.Count + 1; a++)
            {
                for (int b = 0; b < cellsArr.Length; b++)
                {
                    ICell cell = sheet1.GetRow(a).GetCell(b);
                    if (cell != null)
                    {
                        cell.CellStyle = style0;
                    }
                }
            }
            //sheet1.SetDefaultColumnStyle(1,style0);
            //写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string name = $"品种需求";
            return File(ms, "application/vnd.ms-excel", customer.Name + "-" + name + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        }
        /// <summary>
        /// 品种需求年度
        /// </summary>
        public IVarietyYearService VarietyYearService { get; set; }
        public ActionResult varietyYearDemand()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<VarietyYear, bool>> predicate = FilterHelper.GetExpression<VarietyYear>(request.FilterGroup);
            int total;
            var data = VarietyYearService.VarietyYears.
                Where(a => a.IsDeleted == false).OrderByDescending(a => a.ParticularYear).
                Where<VarietyYear, Guid>(predicate, request.PageCondition, out total, false)
                .ToList().MapToList<VarietyYear, VarietyYearDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }
        /// <summary>
        /// 导出品种需求年份
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public FileResult ExportVarietyYear(Guid customerId)
        {
            var list = VarietyYearService.VarietyYears.Where(a => a.CustomerId == customerId && !a.IsDeleted).ToList().MapToList<VarietyYear, VarietyYearDto>();
            var customer = CustomerService.Customers.FirstOrDefault(a => a.Id == customerId);
            // 创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet($"{customer.Name}");
            ////设置默认行高
            sheet1.DefaultRowHeight = 18 * 20;
            string[] cellsArr = new string[]{
                "需求年份",
                "产品/零件名称",
                "原料材质",
                "原料规格",
                "原料单件重量(kg)",
                "当年需求量(吨)",
                "原料单件高度",
                "原料单件厚度",
                "原料单件宽度",
                "对应攀钢品种",
                "攀钢钒可覆盖量(吨)",
                "攀钢钒已供应量(吨)",
                "攀钢可覆盖量(吨)",
                "攀钢已供应量(吨)",
                "当年预计需求(吨)",
                "次年预计需求量(吨)"
            };
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            for (int i = 0; i < cellsArr.Length; i++)
            {
                row1.CreateCell(i).SetCellValue(cellsArr[i]);
            }

            for (int i = 0; i < list.Count; i++)
            {
                var item = list[i];
                //第一个TR
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(item.ParticularYear + "");
                rowtemp.CreateCell(1).SetCellValue(item.DemandSourcesName);
                rowtemp.CreateCell(2).SetCellValue(item.MaterialMade);
                rowtemp.CreateCell(3).SetCellValue(item.MaterialSpecs);
                rowtemp.CreateCell(4).SetCellValue(item.MaterialWeight + "");
                rowtemp.CreateCell(5).SetCellValue(item.NowNeedWeight + "");
                rowtemp.CreateCell(6).SetCellValue(item.MaterialHeight + "");
                rowtemp.CreateCell(7).SetCellValue(item.MaterialThickness + "");
                rowtemp.CreateCell(8).SetCellValue(item.MaterialWidth + "");
                rowtemp.CreateCell(9).SetCellValue(item.BreedName);
                rowtemp.CreateCell(10).SetCellValue(item.PGFCoverWeight + "");
                rowtemp.CreateCell(11).SetCellValue(item.PGFAlreadySupplyWeight + "");
                rowtemp.CreateCell(12).SetCellValue(item.PGCoverWeight + "");
                rowtemp.CreateCell(13).SetCellValue(item.PGAlreadySupplyWeight + "");
                rowtemp.CreateCell(14).SetCellValue(item.YearNeedProfiles + "");
                rowtemp.CreateCell(15).SetCellValue(item.NextYearNeedProfiles + "");
            }

            //单元格对齐样式
            ICellStyle style0 = book.CreateCellStyle();
            style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//居中 
            style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            for (int a = 0; a < list.Count + 1; a++)
            {
                for (int b = 0; b < cellsArr.Length; b++)
                {
                    ICell cell = sheet1.GetRow(a).GetCell(b);
                    if (cell != null)
                    {
                        cell.CellStyle = style0;
                    }
                }
            }
            //sheet1.SetDefaultColumnStyle(1,style0);
            //写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string name = $"品种需求(历年)";
            return File(ms, "application/vnd.ms-excel", customer.Name + "-" + name + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        }
        /// <summary>
        /// 服务管理
        /// </summary>
        public IServeMgService ServeMgService { get; set; }
        public ActionResult serviceManage()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<ServeMg, bool>> predicate = FilterHelper.GetExpression<ServeMg>(request.FilterGroup);
            int total;
            var data = ServeMgService.Item.
                Where<ServeMg, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).ToList().MapToList<ServeMg, ServeMgDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 顾客归属
        /// </summary>
        public IAscriptionService AscriptionService { get; set; }
        public ActionResult customerAttach()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<Ascription, bool>> predicate = FilterHelper.GetExpression<Ascription>(request.FilterGroup);
            int total;
            var data = AscriptionService.Item.
                Where<Ascription, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false)
                .Select(s => new
                {
                    s.Id,
                    s.DeputyName,
                    s.Organization.Name
                }).ToList();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 竞争对手
        /// </summary>
        public ICompetitorService CompetitorService { get; set; }
        public ActionResult competitor()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<Competitor, bool>> predicate = FilterHelper.GetExpression<Competitor>(request.FilterGroup);
            int total;
            var data = CompetitorService.Item.
                Where<Competitor, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).Select(s => new
                {
                    s.Id,
                    s.CustomerId,
                    s.ContractDelivery,
                    s.ContractDeliveryPath,
                    s.MarketStrategy,
                    s.SupplyRelation,
                    s.ProQualityLevel,
                    s.SupplyDesc,
                    s.Problem,
                    s.ProductShare,
                    s.Name
                }).ToList();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }
        /// <summary>
        /// 顾客简介
        /// </summary>
        public ICustomerProfilesService CustomerProfilesService { get; set; }
        public ActionResult CustomerProfiles()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<CustomerProfiles, bool>> predicate = FilterHelper.GetExpression<CustomerProfiles>(request.FilterGroup);
            int total;
            var data = CustomerProfilesService.Item.
                Where<CustomerProfiles, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).OrderByDescending(a => a.YearProfiles).Select(s => new
                {
                    s.Id,
                    s.YearProfiles,
                    s.Profiles
                }).ToList();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }
        /// <summary>
        /// 推广计划
        /// </summary>
        public IPromotionPlanService PromotionPlanService { get; set; }
        public ActionResult PromotionPlans()
        {
            GridRequest request = new GridRequest(Request);
            Expression<Func<PromotionPlan, bool>> predicate = FilterHelper.GetExpression<PromotionPlan>(request.FilterGroup);
            int total;
            var data = PromotionPlanService.PromotionPlans.
                Where<PromotionPlan, Guid>(predicate, request.PageCondition, out total).
                Where(a => a.IsDeleted == false).OrderByDescending(a => a.CreatedTime).ToList().MapToList<PromotionPlan, PromotionPlanDto>();
            return Json(new GridData<object>(data, total), JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 导出客户信息
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public IOrganizationalStructureService organizationalStructureService { get; set; }
        public FileResult ExportCustomer()
        {
            var list = CustomerService.Customers.Where(a => a.IsDeleted == false).Select(s => new
            {
                s.Name,
                s.ShortName,
                s.CustomerAddress,
                s.Business,
                s.Industry,
                s.LegalMan,
                LinkManCount = s.LinkMans.Where(a => a.IsDeleted == false).Count(),
                CustomerNatureCount = s.CustomerNatures.Count(),
                TechnologyCount = s.Technologys.Count(),
                ProductCount = s.Products.Count(),
                PurchaseCount = s.Purchases.Count(),
                VarietyCount = s.Varietys.Count(),
                ServeMgCount = s.ServeMgs.Count(),
                AscriptionCount = s.Ascriptions.Count(),
                CompetitorCount = s.Competitors.Count(),
                CustomerProfileCount = s.CustomerProfiles.Count(),
                CustomerAgreementCount = s.CustomerAgreements.Count(),
                PromotionPlanCount = s.PromotionPlans.Count(),
                s.CreatedTime,
                s.CreateId,
                s.CreateLogCode,
                s.CreateName,
                s.LastEditId,
                s.LastEditLogCode,
                s.LastEditName,
                s.LastEditTime
            }).ToList();
            // 创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet($"客户信息");
            ////设置默认行高
            sheet1.DefaultRowHeight = 18 * 20;
            sheet1.DefaultColumnWidth = 30;
            List<string> cellsArr = new List<string>(){
                "顾客名称",
                "顾客简称",
                "注册省",
                "注册市",
                "注册区",
                "注册地址",
                "主营业务",
                "所属行业",
                "法人代表",
                "联系人(总数)",
                "顾客性质(总数)",
                "工艺流程(总数)",
                "产品零件/销售品种(总数)",
                "采购渠道(总数)",
                "品种需求(总数)",
                "服务管理(总数)",
                "顾客归属(总数)",
                "竞争对手(总数)",
                "顾客简介(总数)",
                "顾客协议(总数)",
                "推广计划(总数)",
                "录入人机构",
                "录入人",
                "录入时间",
                "修改人机构",
                "修改人",
                "修改时间"
            };
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            for (int i = 0; i < cellsArr.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(cellsArr[i]);
            }
            var organization = organizationalStructureService.OrganizationalStructures.Select(s => new { s.JobNumber, s.Organizational }).ToList();
            for (int i = 0; i < list.Count; i++)
            {
                var item = list[i];
                //第一个TR
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(cellsArr.IndexOf("顾客名称")).SetCellValue(item.Name);
                rowtemp.CreateCell(cellsArr.IndexOf("顾客简称")).SetCellValue(item.ShortName);
                rowtemp.CreateCell(cellsArr.IndexOf("注册省")).SetCellValue(item.CustomerAddress?.Province ?? "");
                rowtemp.CreateCell(cellsArr.IndexOf("注册市")).SetCellValue(item.CustomerAddress?.City ?? "");
                rowtemp.CreateCell(cellsArr.IndexOf("注册区")).SetCellValue(item.CustomerAddress?.Borough ?? "");
                rowtemp.CreateCell(cellsArr.IndexOf("注册地址")).SetCellValue(item.CustomerAddress?.Description ?? "");
                rowtemp.CreateCell(cellsArr.IndexOf("主营业务")).SetCellValue(item.Business);
                rowtemp.CreateCell(cellsArr.IndexOf("所属行业")).SetCellValue(item.Industry);
                rowtemp.CreateCell(cellsArr.IndexOf("法人代表")).SetCellValue(item.LegalMan);
                rowtemp.CreateCell(cellsArr.IndexOf("联系人(总数)")).SetCellValue(item.LinkManCount);
                rowtemp.CreateCell(cellsArr.IndexOf("顾客性质(总数)")).SetCellValue(item.CustomerNatureCount);
                rowtemp.CreateCell(cellsArr.IndexOf("工艺流程(总数)")).SetCellValue(item.TechnologyCount);
                rowtemp.CreateCell(cellsArr.IndexOf("产品零件/销售品种(总数)")).SetCellValue(item.ProductCount);
                rowtemp.CreateCell(cellsArr.IndexOf("采购渠道(总数)")).SetCellValue(item.PurchaseCount);
                rowtemp.CreateCell(cellsArr.IndexOf("品种需求(总数)")).SetCellValue(item.VarietyCount);
                rowtemp.CreateCell(cellsArr.IndexOf("服务管理(总数)")).SetCellValue(item.ServeMgCount);
                rowtemp.CreateCell(cellsArr.IndexOf("顾客归属(总数)")).SetCellValue(item.AscriptionCount);
                rowtemp.CreateCell(cellsArr.IndexOf("竞争对手(总数)")).SetCellValue(item.CompetitorCount);
                rowtemp.CreateCell(cellsArr.IndexOf("顾客简介(总数)")).SetCellValue(item.CustomerProfileCount);
                rowtemp.CreateCell(cellsArr.IndexOf("顾客协议(总数)")).SetCellValue(item.CustomerAgreementCount);
                rowtemp.CreateCell(cellsArr.IndexOf("推广计划(总数)")).SetCellValue(item.PromotionPlanCount);
                var createUser = organization.FirstOrDefault(a => a.JobNumber == item.CreateLogCode);
                var editUser = organization.FirstOrDefault(a => a.JobNumber == item.LastEditLogCode);
                rowtemp.CreateCell(cellsArr.IndexOf("录入人机构")).SetCellValue(createUser?.Organizational);
                rowtemp.CreateCell(cellsArr.IndexOf("录入人")).SetCellValue(item.CreateName);
                rowtemp.CreateCell(cellsArr.IndexOf("录入时间")).SetCellValue(item.CreatedTime.ToString("yyyy/MM/dd HH:mm"));
                rowtemp.CreateCell(cellsArr.IndexOf("修改人机构")).SetCellValue(editUser?.Organizational);
                rowtemp.CreateCell(cellsArr.IndexOf("修改人")).SetCellValue(item.LastEditName);
                rowtemp.CreateCell(cellsArr.IndexOf("修改时间")).SetCellValue(item.LastEditTime?.ToString("yyyy/MM/dd HH:mm"));
            }

            //单元格对齐样式
            ICellStyle style0 = book.CreateCellStyle();
            style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//居中 
            style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            for (int a = 0; a < list.Count + 1; a++)
            {
                for (int b = 0; b < cellsArr.Count; b++)
                {
                    ICell cell = sheet1.GetRow(a).GetCell(b);
                    if (cell != null)
                    {
                        cell.CellStyle = style0;
                    }
                }
            }
            //sheet1.SetDefaultColumnStyle(1,style0);
            //写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string name = $"客户信息";
            return File(ms, "application/vnd.ms-excel", name + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        }
    }
}